﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyLibary.BIZ;
using System.Data;

namespace MyLibary.DAL
{
    class DAL_Kho
    {
        internal static DAL_Connection connect = new DAL_Connection();
        internal static List<BIZ_Kho> GetAll()
        {
            DataTable tb = connect.ExecuteSelect("SELECT * FROM Kho");
            List<BIZ_Kho> result = new List<BIZ_Kho>();
            for (int i = 0; i < tb.Rows.Count; i++)
                result.Add(new BIZ_Kho(tb.Rows[i][0].ToString(), Convert.ToDateTime(tb.Rows[i][1].ToString()),Convert.ToInt32( tb.Rows[i][2])));
            return result;
        }
        internal static BIZ_Kho getBySanPham(string MaSanPham,DateTime Ngay)
        {
            string formatSQLDate=String.Format("{0:yyyy-MM-dd}",Ngay);
            DataTable tb = connect.ExecuteSelect(String.Format("SELECT * FROM Kho WHERE MaSanPham='{0}' AND Ngay='{1}'",MaSanPham,formatSQLDate));
            if (tb.Rows.Count < 1)
                return null;
            return new BIZ_Kho(tb.Rows[0][0].ToString(), Convert.ToDateTime(tb.Rows[0][1].ToString()), Convert.ToInt32(tb.Rows[0][2].ToString()));
        }
       /// <summary>
       /// lấy số lượng tồn kho của 1 ngày
       /// </summary>
       /// <param name="MaSanPham"></param>
       /// <param name="date"></param>
       /// <returns></returns>
        public static int GetSoLuongTon(string MaSanPham,DateTime date)
        {
            string formatDate = String.Format("{0:yyyy-MM-dd}",date);
            string query = "SELECT SUM(SoLuongTon)  FROM Kho WHERE MaSanPham='" + MaSanPham + "' AND  Ngay<='" + formatDate + "'";
            DataTable tb = connect.ExecuteSelect(query);
            int result = 0;
            try
            {
                result = Convert.ToInt32(tb.Rows[0][0].ToString());
            }
            catch { return 0; }
            return result;
        }
       public static BIZ_Kho getByDate(string MaSanPham,DateTime date)
       {
           string formatDate = String.Format("{0:yyyy-MM-dd}", date);
           DataTable tb=connect.ExecuteSelect("SELECT * FROM Kho WHERE MaSanPham='"+MaSanPham+"' AND Ngay='"+formatDate+"'");
           if (tb.Rows.Count < 1)
               return null;
           return new BIZ_Kho(tb.Rows[0][0].ToString(), Convert.ToDateTime(tb.Rows[0][1].ToString()), Convert.ToInt32(tb.Rows[0][2]));
       }
       /// <summary>
       /// thêm 1 chi tiết kho nếu chưa tồn tại, ngược lại thì update
       /// </summary>
       /// <param name="kho"></param>
       /// <returns></returns>
        public static bool Insert(BIZ_Kho kho)
        {
            string formatDate = String.Format("{0:yyyy-MM-dd}", kho.Ngay);
            BIZ_Kho temp=getByDate(kho.MaSanPham, kho.Ngay);
           if (temp == null)
               return connect.ExecuteUpdate(String.Format("INSERT INTO Kho VALUES('{0}','{1}','{2}')", kho.MaSanPham, formatDate, kho.SoLuongTon));
           return connect.ExecuteUpdate(String.Format("UPDATE Kho SET SoLuongTon={0} WHERE MaSanPham='{1}'", kho.SoLuongTon + temp.SoLuongTon, kho.MaSanPham));
        }
       /// <summary>
       /// cập nhật lại số lượng
       /// </summary>
       /// <param name="kho"></param>
       /// <returns></returns>
        public static bool Update(BIZ_Kho kho)
        {
            string formatDate = String.Format("{0:yyyy-MM-dd}", kho.Ngay);
            return connect.ExecuteUpdate(String.Format("UPDATE Kho SET SoLuongTon={0} WHERE MaSanPham='{1}'", kho.SoLuongTon, kho.MaSanPham));
        }
       /// <summary>
       /// xóa 1 record trong table kho
       /// </summary>
       /// <param name="kho"></param>
       /// <returns></returns>
        public static bool Deleted(BIZ_Kho kho)
        {
            string formatDate = String.Format("{0:yyyy-MM-dd}", kho.Ngay);
            return connect.ExecuteUpdate(String.Format("DELETE Kho WHERE MaSanPham='{0}' AND Ngay='{1}'", kho.MaSanPham, formatDate));
        }
    }
}
